-- This demo shows how LIKE ranges are affected by collation.
-- © Erland Sommarskog 2024.
USE Latin1_General_CI_AS
go
-- One more table with words.
CREATE TABLE morewords (word nvarchar (20) NOT NULL PRIMARY KEY)
INSERT morewords (word)
   VALUES(N'Abraham'), (N'apple'), (N'Berlin'), (N'cigar'), (N'London'),
         (N'Österreich'), (N'purple'), (N'Stockholm'), (N'zuppa')

SELECT * FROM morewords
go
-- We want to find the words that start in uppercase - but we get back all.
SELECT * FROM morewords WHERE word LIKE '[A-Z]%' 
go
-- Some people try a case-sensitive collation.
SELECT * FROM morewords
WHERE word COLLATE Latin1_General_CS_AS LIKE '[A-Z]%' 
ORDER BY word COLLATE Latin1_General_CS_AS 
-- All words come back but apple. In a CS collation, case only has a secondary
-- weight.
go
-- Here is the trick: use a binary collation.
SELECT * FROM morewords
WHERE word  COLLATE Latin1_General_BIN2 LIKE '[A-Z]%' 
ORDER BY word
go

-- Although to get all uppercase in this example, we need to add the
-- German characters beyond Z separately.
SELECT * FROM morewords
WHERE word  COLLATE Latin1_General_BIN2 LIKE '[A-ZÄÖÜ]%' 
ORDER BY word
